﻿using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace BooksSample
{
    public class QuerySamples
    {
        /// <summary>
        /// 基本查询，查询DbSet的上下文属性将返回指定表的所有实体列表
        /// </summary>
        /// <returns></returns>
        public static async Task QueryAllBooksAsync()
        {
            Console.WriteLine(nameof(QueryAllBooksAsync));
            using (var context = new BooksContext())
            {
                // 异步方式ontext.Books返回所有Book记录并转换为列表
                List<Book> books = await context.Books.ToListAsync();              
                foreach (var b in books)
                {
                    Console.WriteLine(b);
                }
            }
            Console.WriteLine();
        }

        public static async Task QueryAllBooksWithAsyncEnumerableAsync()
        {
            Console.WriteLine(nameof(QueryAllBooksWithAsyncEnumerableAsync));
            using (var context = new BooksContext())
            {
                //ToAsyncEnumerable异步获取所有记录转换为可数集
                //异步遍历所有元素输出到控制台
                await context.Books.ToAsyncEnumerable()
                    .ForEachAsync(b =>
                    {
                        Console.WriteLine(b);
                    });
            }
            Console.WriteLine();
        }

        /// <summary>
        /// 通过Key异步查询
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static async Task QueryBookByKeyAsync(int id)
        {
            Console.WriteLine(nameof(QueryBookByKeyAsync));
            using (var context = new BooksContext())
            {
                //FindAsync查找指定key的记录，如果没有返回null
                Book b = await context.Books.FindAsync(id);
                if (b != null)
                {
                    Console.WriteLine($"found book {b}");
                }
            }
            Console.WriteLine();
        }

        public static async Task QueryBooksAsync()
        {
            using (var context = new BooksContext())
            {
                //条件查询，符合条件的集合
                List<Book> wroxBooks = await context.Books
                    .Where(b => b.Publisher == "Wrox Press")
                    .ToListAsync();

                foreach (var b in wroxBooks)
                {
                    Console.WriteLine($"{b.Title} {b.Publisher}");
                }
            }
            Console.WriteLine();
        }

        public static async Task QueryBookAsync(string title)
        {
            Console.WriteLine(nameof(QueryBookAsync));
            try
            {
                using (var context = new BooksContext())
                {
                    //查询符合条件的第一个元素，找不到的情况返回null
                    Book book = await context.Books.FirstOrDefaultAsync(b => b.Title == title);
                    if (book != null)
                    {
                        Console.WriteLine($"found book {book}");
                    }
                }
            }
            catch (InvalidOperationException ex) when (ex.HResult == -2146233079) // 异常过滤处理器more than 1 element
            {
                Console.WriteLine(ex.Message);
            }
            Console.WriteLine();
        }

        public static async Task FilterBooksAsync(string title)
        {
            Console.WriteLine(nameof(FilterBooksAsync));
            using (var context = new BooksContext())
            {
                //模糊查询
                List<Book> wroxBooks = await context.Books
                    .Where(b => b.Title.Contains(title))
                    .ToListAsync();

                foreach (var b in wroxBooks)
                {
                    Console.WriteLine($"{b.Title} {b.Publisher}");
                }
            }
            Console.WriteLine();
        }
        /// <summary>
        /// 客户端和服务器端求值
        /// </summary>
        public static void ClientAndServerEvaluation()
        {
            Console.WriteLine(nameof(ClientAndServerEvaluation));
            try
            {
                using (var context = new BooksContext())
                {
                    //查询以Pro开头，且以标题排序的书，并且将对应的作者也显示出来
                    var books = context.Books.Where(b => b.Title.StartsWith("Pro"))
                        .OrderBy(b => b.Title)
                        .Select(b => new
                        {
                            b.Title,
                            Authors = string.Join(", ", b.BookAuthors.Select(a => $"{a.Author.FirstName} {a.Author.LastName}").ToArray())
                            //  Authors = b.BookAuthors  // client evaluation
                        });

                    foreach (var b in books)
                    {
                        Console.WriteLine($"{b.Title} {b.Authors}");
                    }
                }
            }
            catch (InvalidOperationException ex) when (ex.HResult == -2146233079)
            {
                Console.WriteLine(ex.Message);
            }
            Console.WriteLine();
        }

        /// <summary>
        /// 原始SQL查询
        /// </summary>
        /// <param name="publisher"></param>
        /// <returns></returns>
        public static async Task RawSqlQuery(string publisher)
        {
            Console.WriteLine(nameof(RawSqlQuery));
            using (var context = new BooksContext())
            {
                //原始context.Books.FromSql查询
                IList<Book> books = await context.Books.FromSql($"SELECT * FROM Books WHERE Publisher = {publisher}").ToListAsync();

                foreach (var b in books)
                {
                    Console.WriteLine($"{b.Title} {b.Publisher}");
                }
            }
            Console.WriteLine();
        }

        public static async Task UseEFCunctions(string titleSegment)
        {
            Console.WriteLine(nameof(UseEFCunctions));
            using (var context = new BooksContext())
            {
                string likeExpression = $"%{titleSegment}%";

                IList<Book> books = await context.Books.Where(b => EF.Functions.Like(b.Title, likeExpression)).ToListAsync();
                foreach (var b in books)
                {
                    Console.WriteLine($"{b.Title} {b.Publisher}");
                }
            }
            Console.WriteLine();
        }

        public static void CompileQuery()
        {
            Console.WriteLine(nameof(CompileQuery));
            Func<BooksContext, string, IEnumerable<Book>> query = EF.CompileQuery<BooksContext, string, Book>((context, publisher) => context.Books.Where(b => b.Publisher == publisher));

            using (var context = new BooksContext())
            {
                IEnumerable<Book> books = query(context, "Wrox Press");

                foreach (var b in books)
                {
                    Console.WriteLine($"{b.Title} {b.Publisher}");
                }
            }
            Console.WriteLine();
        }

        public static async Task CompileQueryAsync()
        {
            Console.WriteLine(nameof(CompileQueryAsync));
            Func<BooksContext, string, AsyncEnumerable<Book>> query = EF.CompileAsyncQuery<BooksContext, string, Book>((context, publisher) => context.Books.Where(b => b.Publisher == publisher));

            using (var context = new BooksContext())
            {
                AsyncEnumerable<Book> books = query(context, "Wrox Press");
                await books.ForEachAsync(b =>
                {
                    Console.WriteLine($"{b.Title} {b.Publisher}");
                });
            }

            Console.WriteLine();
        }
    }
}
